﻿--drop view VPreviewStoreGoods
create view VPreviewStoreGoods as 
select tmp2.NewGoodsCode,tmp2.SpecificationType, tmp2.AmountTax,tmp2.AmountNotTax,tmp2.Quantity,t.NewGoodsName,
t.OldGoodsCode,t.SafeStoreQty,isnull(t.StoreLocation,'') StoreLocation, t.ChargeID,t.ChargeName,
case Quantity when 0 then tmp2.PriceNotTax else round(AmountNotTax/Quantity,2) end as PriceNotTax, 
case Quantity when 0 then tmp2.PriceTax  else round(AmountTax/Quantity,2) end as PriceTax,
case AmountNotTax when 0 then tmp2.TaxRate else round(AmountTax/AmountNotTax,2) end as TaxRate
from
(
			select NewGoodsCode,SpecificationType,round(SUM(AmountTax),2) AmountTax,round(SUM(AmountNotTax),2) 
			AmountNotTax,round(SUM(Quantity),2) Quantity,0 as PriceNotTax,0 as PriceTax,0 as TaxRate
			from  
			(
						select i.NewGoodsCode,i.SpecificationType,i.AmountTax,i.AmountNotTax,i.Quantity from dbo.InStoreBillDtl i, dbo.InStoreBill im 
						where i.BillID=im.BillID and im.BillState='0'
						union all
						select o.NewGoodsCode,o.SpecificationType,-o.AmountTax AmountNotTax,-o.AmountNotTax AmountNotTax,-o.Quantity Quantity from dbo.OutStoreBillDtl o, dbo.OutStoreBill om
						 where o.BillID=om.BillID and om.BillState='0'
						union all
						select g.NewGoodsCode,g.SpecificationType,g.AmountTax,g.AmountNotTax,g.Quantity from dbo.StoreGoods g
			)tmp group by NewGoodsCode,SpecificationType having SUM(Quantity) != 0  --此处处理不等于0的情况
			union--处理当库存为零的时候，单价和税率从库存表中拿取
			select tmp2.*,isnull(g.PriceNotTax,0) PriceNotTax,isnull(g.PriceTax,0) PriceTax,isnull(g.TaxRate,0) TaxRate,0 as AmountTax, 0 as AmountNotTax
			from
			(
						select NewGoodsCode,SpecificationType,round(SUM(Quantity),2) Quantity
						from  
						(
									select i.NewGoodsCode,i.SpecificationType,i.Quantity from dbo.InStoreBillDtl i, dbo.InStoreBill im where i.BillID=im.BillID and im.BillState='0'
									union all
									select o.NewGoodsCode,o.SpecificationType,-o.Quantity Quantity from dbo.OutStoreBillDtl o, dbo.OutStoreBill om where o.BillID=om.BillID-- and om.BillState='0'
									union all
									select g.NewGoodsCode,g.SpecificationType,g.Quantity from dbo.StoreGoods g
						)tmp group by NewGoodsCode,SpecificationType having SUM(Quantity) = 0 --此处处理等于0的情况
			) tmp2 left join dbo.StoreGoods g on tmp2.NewGoodsCode=g.NewGoodsCode 
) tmp2,dbo.GoodsCodeType t where tmp2.NewGoodsCode=t.NewGoodsCode

go

create view VGetDate 
as 
select getdate() as dt;
go

create view VInStoreBillDtlAndHead
as
select d.*,h.InBillDate,h.PurchaseID,h.PurchaseName,h.KeeperID,h.KeeperName,h.CheckerID,h.CheckerName,
h.BillState,h.ContractNo,h.ProjectNo,h.ProviderCode,h.ProviderName,h.Remark,h.RequisitionNo
 from InStoreBillDtl d,InStoreBill h
where d.BillID = h.BillID
go